/*  Script used to create functions that are used by the
	NetSys application and stored procedures.
*/

USE [NETSYS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* Gets the number of correct answers for a quiz attempt */
CREATE FUNCTION [dbo].[GetQuizCorrect](@ATTEMPT_ID int)
RETURNS int
AS
BEGIN
	DECLARE @CORRECT int
	SELECT @CORRECT=COUNT(*) FROM QUESTION_RESULT WHERE ATTEMPT_ID=@ATTEMPT_ID AND ANSWERED_CORRECTLY=1
	return @CORRECT
END
GO

/* Gets the number of incorrect answers for a quiz attempt */
CREATE FUNCTION [dbo].[GetQuizIncorrect](@ATTEMPT_ID int)
RETURNS int
AS
BEGIN
	DECLARE @CORRECT int
	SELECT @CORRECT=COUNT(*) FROM QUESTION_RESULT WHERE ATTEMPT_ID=@ATTEMPT_ID AND ANSWERED_CORRECTLY=0
	return @CORRECT
END
GO

/* Gets the number of questions for a certain quiz */
CREATE FUNCTION [dbo].[GetQuizNumQuestions](@QUIZ_ID int)
RETURNS int
AS
BEGIN
	DECLARE @QUESTION_COUNT int
	SELECT @QUESTION_COUNT=COUNT(*) FROM QUIZ_QUESTION WHERE QUIZ_ID=@QUIZ_ID
	return @QUESTION_COUNT
END
GO